package com.jeesite.modules.utils;

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Random;

public class ExcelUtils {


	/**
	 * 创建行元素
	 * @param style    样式
	 * @param height   行高
	 * @param value    行显示的内容
	 * @param row1     起始行
	 * @param row2     结束行
	 * @param col1     起始列
	 * @param col2     结束列
	 */
	private static void createRow( HSSFSheet sheet ,HSSFCellStyle style, int height, String value, int row1, int row2, int col1, int col2){

		sheet.addMergedRegion(new CellRangeAddress(row1, row2, col1, col2));  //设置从第row1行合并到第row2行，第col1列合并到col2列
		HSSFRow rows = sheet.createRow(row1);        //设置第几行
		rows.setHeight((short) height);              //设置行高
		HSSFCell cell = rows.createCell(col1);       //设置内容开始的列
		cell.setCellStyle(style);                    //设置样式
		cell.setCellValue(value);                    //设置该行的值
	}

	/**
	 * 创建样式
	 * @param fontSize   字体大小
	 * @param align  水平位置  左右居中2 居右3 默认居左 垂直均为居中
	 * @param bold   是否加粗
	 * @param border  是否需要边框
	 * @param color
	 * @return
	 */
	private static HSSFCellStyle getStyle(HSSFWorkbook workbook,int fontSize,
										  int align,boolean bold,
										  boolean border,boolean color){
		HSSFFont font = workbook.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) fontSize);// 字体大小
		if (bold){
			font.setBold(true);
		}
		if (color){
			font.setColor(HSSFFont.COLOR_RED);
		}
		HSSFCellStyle style = workbook.createCellStyle();
		style.setFont(font);                         //设置字体
		style.setAlignment(HorizontalAlignment.forInt((short) align));          // 左右居中2 居右3 默认居左
		style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中1
		if (border){
//			style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
//			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
//			style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
//			style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
			style.setBorderRight(BorderStyle.valueOf((short) 2));
			style.setBorderLeft(BorderStyle.valueOf((short) 2));
			style.setBorderBottom(BorderStyle.valueOf((short) 2));
			style.setBorderTop(BorderStyle.valueOf((short) 2));
			style.setLocked(true);
		}
		return style;
	}

	private static HSSFCellStyle getStyleNew(HSSFWorkbook workbook,int fontSize,
										  int align,boolean bold,
										  boolean border,boolean color,HSSFCellStyle style){
		HSSFFont font = workbook.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) fontSize);// 字体大小
		if (bold){
			font.setBold(true);
		}
		if (color){
			font.setColor(HSSFFont.COLOR_RED);
		}
//		HSSFCellStyle style = workbook.createCellStyle();
		style.setFont(font);                         //设置字体
		style.setAlignment(HorizontalAlignment.forInt((short) align));          // 左右居中2 居右3 默认居左
//		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中1
		if (border){
//			style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
//			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
//			style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
//			style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
			style.setBorderRight(BorderStyle.valueOf((short) 2));
			style.setBorderLeft(BorderStyle.valueOf((short) 2));
			style.setBorderBottom(BorderStyle.valueOf((short) 2));
			style.setBorderTop(BorderStyle.valueOf((short) 2));
			style.setLocked(true);
		}
		return style;
	}





	/**
	 * 创建excel输出流
	 * @param response   HttpServletResponse
	 * @param tableName 表名
	 * @param tablesStr  表头数组
	 * @param mapList     筛选后的数据
	 */
	public static void write(HttpServletResponse response, String[] tablesStr, List<Map<String,String>> mapList, String tableName) throws IOException {
		try {
			//声明一个工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();
			//生成一个表格，设置表格名称为"逾期反馈表"
			HSSFSheet sheet = workbook.createSheet(tableName);
			//设置表格列宽度为10个字节
			sheet.setDefaultColumnWidth(30);
			sheet.setColumnWidth(0,10000);
			// 表头标题
			HSSFCellStyle titleStyle = getStyle(workbook,22,2,true,true,true);//样式
			createRow(sheet,titleStyle,1000,tableName,0,0,0,tablesStr.length-1);

			//创建第一行表头
			HSSFRow headrow = sheet.createRow(1);
			//遍历添加表头
			for (int i = 0; i < tablesStr.length; i++) {
				//创建一个单元格
				HSSFCell cell = headrow.createCell(i);
				//创建一个内容对象
				HSSFRichTextString text = new HSSFRichTextString(tablesStr[i]);
				//将内容对象的文字内容写入到单元格中并设置单元格格式
				cell.setCellStyle(getStyle(workbook,15,2,true,true,false));
				cell.setCellValue(text);
			}
			int rownum = 2;
			for (Map<String, String> map : mapList) {
				HSSFRow row = sheet.createRow(rownum);
				for (int i = 0; i < tablesStr.length; i++) {
					HSSFCell cell = row.createCell(i);
					HSSFRichTextString text = new HSSFRichTextString( map.get(tablesStr[i]));
					cell.setCellStyle(getStyle(workbook,12,rownum==2?1:2,false,true,false));
					cell.setCellValue(text);
				}
				rownum++;
			}
			System.out.println(String.format("attachment;filename=%s%s.xls", URLEncoder.encode(tableName, "UTF-8"), "yyyy-MM-dd HH:mm"));
			//准备将Excel的输出流通过response输出到页面下载
			//八进制输出流
			response.setContentType("application/octet-stream");
			//这后面可以设置导出Excel的名称
			response.setHeader("Content-disposition", String.format("attachment;filename=%s%s.xls",URLEncoder.encode(tableName,"UTF-8"), "yyyy-MM-dd HH:mm"));


			response.flushBuffer();

			workbook.write(response.getOutputStream());

		} catch ( IOException e) {

			throw new IOException("导出异常", e);
		}
	}

    /**
     * 导出文件到本地
     * @param tablesStr
     * @param mapList
     * @param tableName
     * @param path
     * @return
     */
	public static String write(String[] tablesStr, List<Map<String,String>> mapList, String tableName, String path) throws IOException {
        File efile = null;
	    FileOutputStream fos = null;
	    try {
			//声明一个工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();
			//生成一个表格，设置表格名称为"逾期反馈表"
			HSSFSheet sheet = workbook.createSheet(tableName);
			//设置表格列宽度为10个字节
			sheet.setDefaultColumnWidth(30);
			sheet.setColumnWidth(0,10000);
			// 表头标题
			HSSFCellStyle titleStyle = getStyle(workbook,22,2,true,true,true);//样式
			createRow(sheet,titleStyle,1000,tableName,0,0,0,tablesStr.length-1);

			//创建样式
			HSSFFont font = workbook.createFont();
			font.setFontName("宋体");
			font.setFontHeightInPoints((short) 15);// 字体大小
			font.setBold(true);
			HSSFCellStyle style = workbook.createCellStyle();
			style.setFont(font);                         //设置字体
			style.setAlignment(HorizontalAlignment.forInt((short) 2));          // 左右居中2 居右3 默认居左
			style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中1
			if (true){
				style.setBorderRight(BorderStyle.valueOf((short) 2));
				style.setBorderLeft(BorderStyle.valueOf((short) 2));
				style.setBorderBottom(BorderStyle.valueOf((short) 2));
				style.setBorderTop(BorderStyle.valueOf((short) 2));
				style.setLocked(true);
			}

			//创建第一行表头
			HSSFRow headrow = sheet.createRow(1);
			//遍历添加表头
			for (int i = 0; i < tablesStr.length; i++) {
				//创建一个单元格
				HSSFCell cell = headrow.createCell(i);
				//创建一个内容对象
				HSSFRichTextString text = new HSSFRichTextString(tablesStr[i]);
				//将内容对象的文字内容写入到单元格中并设置单元格格式
				cell.setCellStyle(style);
				cell.setCellValue(text);
			}
			//重新设置第二行及其之后的样式
			int rownum = 2;
			font.setBold(true);
			font.setFontHeightInPoints((short) 12);// 字体大小
			style.setAlignment(HorizontalAlignment.forInt((short) 2));
			for (Map<String, String> map : mapList) {
				HSSFRow row = sheet.createRow(rownum);
				for (int i = 0; i < tablesStr.length; i++) {
					HSSFCell cell = row.createCell(i);
					HSSFRichTextString text = new HSSFRichTextString( map.get(tablesStr[i]));
					cell.setCellStyle(style);
					cell.setCellValue(text);

					//设置显示样式
                    HSSFCellStyle cellStyle = workbook.createCellStyle();
					cellStyle.setWrapText(true);//自动换行
					cellStyle.setAlignment(HorizontalAlignment.LEFT);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    cell.setCellStyle(cellStyle);
				}
				rownum++;
			}

            File dirFile = new File(path);
            if (!dirFile.exists()) {
                dirFile.mkdir();
            }
            efile = new File(path+"/"+ tableName +"-" +"yyyy-MM-dd"+"-"+new Random().nextInt(9999) +".xls");
            fos = new FileOutputStream(efile);
            workbook.write(fos);
		} catch ( IOException e) {
			throw new IOException("导出异常", e);
		} finally {
            if (null != fos) {
                try {
                    fos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
		return efile.getPath();
	}

	/**
	 * 导出文件到本地(无表头)
	 * @param tablesStr
	 * @param mapList
	 * @param tableName
	 * @param path
	 * @return
	 */
	public static String writeNoTableName(String[] tablesStr, List<Map<String,String>> mapList,String tableName, String path) throws IOException {
		File efile = null;
		FileOutputStream fos = null;
		try {
			//声明一个工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();
			//生成一个表格，设置表格名称为"逾期反馈表"
			HSSFSheet sheet = workbook.createSheet();
			//设置表格列宽度为10个字节
			sheet.setDefaultColumnWidth(30);
			sheet.setColumnWidth(0,10000);
			// 表头标题
			HSSFRow headrow = sheet.createRow(0);

			HSSFCellStyle style = workbook.createCellStyle();
			style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中1
			//遍历添加表头
			for (int i = 0; i < tablesStr.length; i++) {
				//创建一个单元格
				HSSFCell cell = headrow.createCell(i);
				//创建一个内容对象
				HSSFRichTextString text = new HSSFRichTextString(tablesStr[i]);
				//将内容对象的文字内容写入到单元格中并设置单元格格式
				cell.setCellStyle(getStyleNew(workbook,15,2,true,true,false,
						style));
				cell.setCellValue(text);
			}
			int rownum = 1;
			HSSFCellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setWrapText(true);//自动换行
			cellStyle.setAlignment(HorizontalAlignment.CENTER);
			cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
			for (Map<String, String> map : mapList) {
				HSSFRow row = sheet.createRow(rownum);
				for (int i = 0; i < tablesStr.length; i++) {
					HSSFCell cell = row.createCell(i);
					HSSFRichTextString text = new HSSFRichTextString( map.get(tablesStr[i]));
					cell.setCellStyle(getStyleNew(workbook,12,rownum==2?1:2,
							false,true,false,style));
					cell.setCellValue(text);
					//设置显示样式
					cell.setCellStyle(cellStyle);
				}
				rownum++;
			}

			File dirFile = new File(path);
			if (!dirFile.exists()) {
				dirFile.mkdir();
			}
			efile = new File(path+"/"+ tableName +"-" +"yyyy-MM-dd"+"-"+new Random().nextInt(9999) +".xls");
			fos = new FileOutputStream(efile);
			workbook.write(fos);
		} catch ( IOException e) {
			throw new IOException("导出异常", e);
		} finally {
			if (null != fos) {
				try {
					fos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return efile.getPath();
	}


	/******************************************读取excel*****************************/
	/**
	 * 根据fileType不同读取excel文件
	 *
	 * @param orginName
	 * @param is
	 * @throws IOException
	 */
	@SuppressWarnings({ "resource"})
	public static List<List<String>> readExcel(String orginName, InputStream is) {
		String fileType = orginName.substring(orginName.lastIndexOf(".") + 1);
		// return a list contains many list
		List<List<String>> lists = new ArrayList<List<String>>();
		//读取excel文件
		try {
			//获取工作薄
			Workbook wb = null;
			if (fileType.equals("xls")) {
				wb = new HSSFWorkbook(is);
			} else if (fileType.equals("xlsx")) {
				wb = new XSSFWorkbook(is);
			} else {
				return null;
			}

			//读取第一个工作页sheet
			Sheet sheet = wb.getSheetAt(0);
			//第一行为标题
			for (Row row : sheet) {
				ArrayList<String> list = new ArrayList<String>();
				for (Cell cell : row) {
					//根据不同类型转化成字符串
					cell.setCellType(CellType.forInt(1));
					if(cell.getStringCellValue().trim().equals("无")) {
						list.add("");
					} else {
						list.add(cell.getStringCellValue());
					}
				}
				lists.add(list);
			}
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (is != null) {
					is.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return lists;
	}
	public  static List<List<JSONObject>> readMergeExcel(String orginName, InputStream is, int sheetIndex, int startReadLine, int tailLine) {
		String fileType = orginName.substring(orginName.lastIndexOf(".") + 1);
		List<List<JSONObject>> results = new ArrayList<>();
		Workbook wb = null;
		try {
			if (fileType.equals("xls")) {
				wb = new HSSFWorkbook(is);
			} else if (fileType.equals("xlsx")) {
				wb = new XSSFWorkbook(is);
			} else {
				return null;
			}

			Sheet sheet = wb.getSheetAt(sheetIndex);
			Row row = null;
			for (int i = startReadLine; i < sheet.getLastRowNum() - tailLine + 1; i++) {
				row = sheet.getRow(i);
				if (row == null)
					continue;
				List<JSONObject> result = new ArrayList<>();
				for (Cell c : row) {
					JSONObject values = new JSONObject();
					boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
					values.put("isMerge",isMerge);
					// 判断是否具有合并单元格
					if (isMerge) {
						JSONObject rs = getMergedRegionJsonValue(sheet, row.getRowNum(), c.getColumnIndex());
						values.putAll(rs);
					} else {
						values.put("cellValue",getCellValue(c));
					}
					result.add(values);
				}
				results.add(result);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return results;
	}


	public  static List<List<JSONObject>> readMergeExcel(String path, int sheetIndex, int startReadLine, int tailLine) {
		List<List<JSONObject>> results = new ArrayList<>();
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(new File(path));
			Sheet sheet = wb.getSheetAt(sheetIndex);
			Row row = null;
			for (int i = startReadLine; i < sheet.getLastRowNum() - tailLine + 1; i++) {
				row = sheet.getRow(i);
				if (row == null)
					continue;
				List<JSONObject> result = new ArrayList<>();
				for (Cell c : row) {
					JSONObject values = new JSONObject();
					boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
					values.put("isMerge",isMerge);
					// 判断是否具有合并单元格
					if (isMerge) {
						JSONObject rs = getMergedRegionJsonValue(sheet, row.getRowNum(), c.getColumnIndex());
						values.putAll(rs);
					} else {
						values.put("cellValue",getCellValue(c));
					}
					result.add(values);
				}
				results.add(result);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return results;
	}

	/**
	 * 获取合并单元格的值
	 */
	public static String getMergedRegionValue(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					Row fRow = sheet.getRow(firstRow);
					Cell fCell = fRow.getCell(firstColumn);
					return getCellValue(fCell);
				}
			}
		}
		return null;
	}

	public  static JSONObject getMergedRegionJsonValue(Sheet sheet, int row, int column) {
		JSONObject values = new JSONObject();
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					Row fRow = sheet.getRow(firstRow);
					Cell fCell = fRow.getCell(firstColumn);
					values.put("cellValue",getCellValue(fCell));
					values.put("firstColumn",firstColumn);
					values.put("lastColumn",lastColumn);
					values.put("firstRow",firstRow);
					values.put("lastRow",lastRow);
					return values;
				}
			}
		}
		return values;
	}

	/**
	 * 判断合并了行
	 */
	private static boolean isMergedRow(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row == firstRow && row == lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * 判断指定的单元格是否是合并单元格
	 * @param row    行下标
	 * @param column 列下标
	 */
	private static  boolean isMergedRegion(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}

	/**
	 * 判断sheet页中是否含有合并单元格
	 */
	private static boolean hasMerged(Sheet sheet) {
		return sheet.getNumMergedRegions() > 0 ? true : false;
	}

	/**
	 * 合并单元格
	 * @param firstRow 开始行
	 * @param lastRow  结束行
	 * @param firstCol 开始列
	 * @param lastCol  结束列
	 */
	private static void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
		sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
	}

	/**
	 * 获取单元格的值
	 */
	public  static String getCellValue(Cell cell) {
		if (cell == null) {return "";}
		if (cell.getCellType() == CellType.STRING) {
			return cell.getStringCellValue();
		}
		if (cell.getCellType() == CellType.BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		}
		if (cell.getCellType() ==  CellType.FORMULA) {
			return cell.getCellFormula();
		}
		if (cell.getCellType() ==  CellType.NUMERIC) {
			String s = String.valueOf(cell.getNumericCellValue());
			if(s.endsWith(".0")){
				String[] split = s.split("\\.");
				return 	split[0];
			}
			return String.valueOf(cell.getNumericCellValue());
		}
		return "";
	}

}
